using System;
using System.Data;
using System.Data.Common;
using System.Configuration;
using System.Data.SqlClient;

using SIS.ENT;

/// <summary>
/// The TeacherDesignationTableDB class is responsible for interacting with the database to retrieve and store information
/// about TeacherDesignationTable objects.
/// </summary>
public class TeacherDesignationTableDB
{
    #region "Public Methods"

    /// <summary>
    /// Gets an instance of TeacherDesignationTable from the underlying datasource.
    /// </summary>
    /// <param name="teacherDesignationKey">The unique TeacherDesignationKey of the TeacherDesignationTable in the database.</param>
    /// <returns>An TeacherDesignationTable when the TeacherDesignationKey was found in the database, or null otherwise.</returns>
    public static TeacherDesignationTable GetItem(int teacherDesignationKey)
    {
        TeacherDesignationTable myTeacherDesignationTable = null;
        using (SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["sis.dbconn"].ConnectionString))
        {
            using (SqlCommand myCommand = new SqlCommand("sprocTeacherDesignationTableSelectSingleItem", myConnection))
            {
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.AddWithValue("@teacherDesignationKey", teacherDesignationKey);

                myConnection.Open();
                using (SqlDataReader myReader = myCommand.ExecuteReader())
                {
                    if (myReader.Read())
                    {
                        myTeacherDesignationTable = FillDataRecord(myReader);
                    }
                    myReader.Close();
                }
                myConnection.Close();
            }
            return myTeacherDesignationTable;
        }
    }

    /// <summary>
    /// Gets an instance of TeacherDesignationTable from the underlying datasource.
    /// </summary>
    /// <param name="teacherDesignationKey">The unique TeacherDesignationKey of the TeacherDesignationTable in the database.</param>
    /// <returns>An TeacherDesignationTable when the TeacherDesignationKey was found in the database, or null otherwise.</returns>
    public static TeacherDesignationTable GetActiveItem(int UserAccountKey)
    {
        TeacherDesignationTable myTeacherDesignationTable = null;
        using (SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["sis.dbconn"].ConnectionString))
        {
            using (SqlCommand myCommand = new SqlCommand("sprocTeacherDesignationTableSelectActiveSingleItem", myConnection))
            {
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.AddWithValue("@UserAccountKey", UserAccountKey);

                myConnection.Open();
                using (SqlDataReader myReader = myCommand.ExecuteReader())
                {
                    if (myReader.Read())
                    {
                        myTeacherDesignationTable = FillDataRecord(myReader);
                    }
                    myReader.Close();
                }
                myConnection.Close();
            }
            return myTeacherDesignationTable;
        }
    }

    
    /// <summary>
    /// Returns a list with TeacherDesignationTable objects.
    /// </summary>
    /// <returns>A generics List with the TeacherDesignationTable objects.</returns>
    public static TeacherDesignationTableList GetList()
    {
        TeacherDesignationTableList tempList = new TeacherDesignationTableList();
        using (SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["sis.dbconn"].ConnectionString))
        {
            using (SqlCommand myCommand = new SqlCommand("sprocTeacherDesignationTableSelectList", myConnection))
            {
                myCommand.CommandType = CommandType.StoredProcedure;

                myConnection.Open();
                using (SqlDataReader myReader = myCommand.ExecuteReader())
                {
                    if (myReader.HasRows)
                    {
                        while (myReader.Read())
                        {
                            tempList.Add(FillDataRecord(myReader));
                        }
                    }
                    myReader.Close();
                }
            }
        }
        return tempList;
    }


    public static TeacherDesignationTableList GetList(int UserAccountKey)
    {
        TeacherDesignationTableList tempList = new TeacherDesignationTableList();
        using (SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["sis.dbconn"].ConnectionString))
        {
            using (SqlCommand myCommand = new SqlCommand("sprocTeacherDesignationTableSelectList", myConnection))
            {
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.AddWithValue("@UserAccountKey", UserAccountKey);

                myConnection.Open();
                using (SqlDataReader myReader = myCommand.ExecuteReader())
                {
                    if (myReader.HasRows)
                    {
                        while (myReader.Read())
                        {
                            tempList.Add(FillDataRecord(myReader));
                        }
                    }
                    myReader.Close();
                }
            }
        }
        return tempList;
    }

    /// <summary>
    /// Returns a list with TeacherDesignationTable objects.
    /// </summary>
    /// <returns>A generics List with the TeacherDesignationTable objects.</returns>
    public static TeacherDesignationTableList GetListByJobDesignationCode(int JobDesignationCode)
    {
        TeacherDesignationTableList tempList = new TeacherDesignationTableList();
        using (SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["sis.dbconn"].ConnectionString))
        {
            using (SqlCommand myCommand = new SqlCommand("sprocTeacherDesignationTableSelectListByJobDesignationCode", myConnection))
            {
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.AddWithValue("@jobDesignationCode", JobDesignationCode);
                myConnection.Open();
                using (SqlDataReader myReader = myCommand.ExecuteReader())
                {
                    if (myReader.HasRows)
                    {
                        while (myReader.Read())
                        {
                            tempList.Add(FillDataRecord(myReader));
                        }
                    }
                    myReader.Close();
                }
            }
            return tempList;
        }
    }

    /// <summary>
    /// Returns a list with TeacherDesignationTable objects.
    /// </summary>
    /// <returns>A generics List with the TeacherDesignationTable objects.</returns>
    public static TeacherDesignationTableList GetListByUserAccountKey(int UserAccountKey)
    {
        TeacherDesignationTableList tempList = new TeacherDesignationTableList();
        using (SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["sis.dbconn"].ConnectionString))
        {
            using (SqlCommand myCommand = new SqlCommand("sprocTeacherDesignationTableSelectListByUserAccountKey", myConnection))
            {
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.AddWithValue("@userAccountKey", UserAccountKey);
                myConnection.Open();
                using (SqlDataReader myReader = myCommand.ExecuteReader())
                {
                    if (myReader.HasRows)
                    {
                        while (myReader.Read())
                        {
                            tempList.Add(FillDataRecord(myReader));
                        }
                    }
                    myReader.Close();
                }
            }
            return tempList;
        }
    }

    /// <summary>
    /// Saves a TeacherDesignationTable in the database.
    /// </summary>
    /// <param name="myTeacherDesignationTable">The TeacherDesignationTable instance to save.</param>
    /// <returns>The new TeacherDesignationKey if the TeacherDesignationTable is new in the database or the existing TeacherDesignationKey when an item was updated.</returns>
    public static int Save(TeacherDesignationTable myTeacherDesignationTable)
    {
        int result = 0;
        using (SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["sis.dbconn"].ConnectionString))
        {
            using (SqlCommand myCommand = new SqlCommand("sprocTeacherDesignationTableInsertUpdateSingleItem", myConnection))
            {
                myCommand.CommandType = CommandType.StoredProcedure;

                if (myTeacherDesignationTable.TeacherDesignationKey == -1)
                {
                    myCommand.Parameters.AddWithValue("@teacherDesignationKey", DBNull.Value);
                }
                else
                {
                    myCommand.Parameters.AddWithValue("@teacherDesignationKey", myTeacherDesignationTable.TeacherDesignationKey);
                }
                if (myTeacherDesignationTable.UserAccountKey == null)
                {
                    myCommand.Parameters.AddWithValue("@userAccountKey", DBNull.Value);
                }
                else
                {
                    myCommand.Parameters.AddWithValue("@userAccountKey", myTeacherDesignationTable.UserAccountKey);
                }
                if (myTeacherDesignationTable.JobDesignationCode == null)
                {
                    myCommand.Parameters.AddWithValue("@jobDesignationCode", DBNull.Value);
                }
                else
                {
                    myCommand.Parameters.AddWithValue("@jobDesignationCode", myTeacherDesignationTable.JobDesignationCode);
                }
                if (myTeacherDesignationTable.JobDescriptionDetail == null)
                {
                    myCommand.Parameters.AddWithValue("@jobDescriptionDetail", DBNull.Value);
                }
                else
                {
                    myCommand.Parameters.AddWithValue("@jobDescriptionDetail", myTeacherDesignationTable.JobDescriptionDetail);
                }
                if (myTeacherDesignationTable.StartDate == null)
                {
                    myCommand.Parameters.AddWithValue("@startDate", DBNull.Value);
                }
                else
                {
                    myCommand.Parameters.AddWithValue("@startDate", myTeacherDesignationTable.StartDate);
                }
                if (myTeacherDesignationTable.EndDate == null)
                {
                    myCommand.Parameters.AddWithValue("@endDate", DBNull.Value);
                }
                else
                {
                    myCommand.Parameters.AddWithValue("@endDate", myTeacherDesignationTable.EndDate);
                }
                if (myTeacherDesignationTable.Remarks == null)
                {
                    myCommand.Parameters.AddWithValue("@remarks", DBNull.Value);
                }
                else
                {
                    myCommand.Parameters.AddWithValue("@remarks", myTeacherDesignationTable.Remarks);
                }
                if (myTeacherDesignationTable.Active == null)
                {
                    myCommand.Parameters.AddWithValue("@active", DBNull.Value);
                }
                else
                {
                    myCommand.Parameters.AddWithValue("@active", myTeacherDesignationTable.Active);
                }
                if (myTeacherDesignationTable.Status == null)
                {
                    myCommand.Parameters.AddWithValue("@status", DBNull.Value);
                }
                else
                {
                    myCommand.Parameters.AddWithValue("@status", myTeacherDesignationTable.Status);
                }
                if (myTeacherDesignationTable.CreatedDateTime == null)
                {
                    myCommand.Parameters.AddWithValue("@createdDateTime", DBNull.Value);
                }
                else
                {
                    myCommand.Parameters.AddWithValue("@createdDateTime", myTeacherDesignationTable.CreatedDateTime);
                }
                if (myTeacherDesignationTable.ModifiedDateTime == null)
                {
                    myCommand.Parameters.AddWithValue("@modifiedDateTime", DBNull.Value);
                }
                else
                {
                    myCommand.Parameters.AddWithValue("@modifiedDateTime", myTeacherDesignationTable.ModifiedDateTime);
                }

                DbParameter returnValue;
                returnValue = myCommand.CreateParameter();
                returnValue.Direction = ParameterDirection.ReturnValue;
                myCommand.Parameters.Add(returnValue);

                myConnection.Open();
                myCommand.ExecuteNonQuery();
                result = Convert.ToInt32(returnValue.Value);
                myConnection.Close();
            }
        }
        return result;
    }

    /// <summary>
    /// Deletes a TeacherDesignationTable from the database.
    /// </summary>
    /// <param name="teacherDesignationKey">The TeacherDesignationKey of the TeacherDesignationTable to delete.</param>
    /// <returns>Returns true when the object was deleted successfully, or false otherwise.</returns>
    public static bool Delete(int teacherDesignationKey)
    {
        int result = 0;
        using (SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["sis.dbconn"].ConnectionString))
        {
            using (SqlCommand myCommand = new SqlCommand("sprocTeacherDesignationTableDeleteSingleItem", myConnection))
            {
                myCommand.CommandType = CommandType.StoredProcedure;

                myCommand.Parameters.AddWithValue("@teacherDesignationKey", teacherDesignationKey);
                myConnection.Open();
                result = myCommand.ExecuteNonQuery();
                myConnection.Close();
            }
        }
        return result > 0;
    }

    #endregion

    /// <summary>
    /// Initializes a new instance of the TeacherDesignationTable class and fills it with the data fom the IDataRecord.
    /// </summary>
    private static TeacherDesignationTable FillDataRecord(IDataRecord myDataRecord)
    {
        TeacherDesignationTable myTeacherDesignationTable = new TeacherDesignationTable();
        if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("TeacherDesignationKey")))
        {
            myTeacherDesignationTable.TeacherDesignationKey = myDataRecord.GetInt32(myDataRecord.GetOrdinal("TeacherDesignationKey"));
        }
        if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("UserAccountKey")))
        {
            myTeacherDesignationTable.UserAccountKey = myDataRecord.GetInt32(myDataRecord.GetOrdinal("UserAccountKey"));
        }
        if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("JobDesignationCode")))
        {
            myTeacherDesignationTable.JobDesignationCode = myDataRecord.GetInt16(myDataRecord.GetOrdinal("JobDesignationCode"));
        }
        if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("JobDescriptionDetail")))
        {
            myTeacherDesignationTable.JobDescriptionDetail = myDataRecord.GetString(myDataRecord.GetOrdinal("JobDescriptionDetail"));
        }
        if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("StartDate")))
        {
            myTeacherDesignationTable.StartDate = myDataRecord.GetDateTime(myDataRecord.GetOrdinal("StartDate"));
        }
        if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("EndDate")))
        {
            myTeacherDesignationTable.EndDate = myDataRecord.GetDateTime(myDataRecord.GetOrdinal("EndDate"));
        }
        if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("Remarks")))
        {
            myTeacherDesignationTable.Remarks = myDataRecord.GetString(myDataRecord.GetOrdinal("Remarks"));
        }
        if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("Active")))
        {
            myTeacherDesignationTable.Active = myDataRecord.GetBoolean(myDataRecord.GetOrdinal("Active"));
        }
        if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("Status")))
        {
            myTeacherDesignationTable.Status = myDataRecord.GetInt16(myDataRecord.GetOrdinal("Status"));
        }
        if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("CreatedDateTime")))
        {
            myTeacherDesignationTable.CreatedDateTime = myDataRecord.GetDateTime(myDataRecord.GetOrdinal("CreatedDateTime"));
        }
        if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("ModifiedDateTime")))
        {
            myTeacherDesignationTable.ModifiedDateTime = myDataRecord.GetDateTime(myDataRecord.GetOrdinal("ModifiedDateTime"));
        }
        return myTeacherDesignationTable;
    }
}
